import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class ExamRepository {

    QueryUtil queryUtil = new QueryUtil();
    DeleteUtil deleteUtil = new DeleteUtil();
    AddUtil addUtil = new AddUtil();
    UpdateUtil updateUtil = new UpdateUtil();
    Scanner sc_exam = new Scanner(System.in);

    public void queryAllStudents(){
        String sql = queryUtil.getQueryAllSql()+"student";
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Student tempStudent = new Student(rs.getInt("student_id"),
                        rs.getString("name"), rs.getInt("age"), rs.getString("sex"));
                System.out.println(tempStudent);
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryStudentByName(){
        String sql = queryUtil.getQueryStudentScoreByNameSql();
        System.out.println("Please enter the name of student: ");
        String name = sc_exam.next();
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            while(rs.next()){
                System.out.println(String.format("姓名：%s, 课程：%s， 分数：%d",
                        rs.getString("name"), rs.getString("class_name"), rs.getInt("score")));
            }
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryStudentByTeacher(){
        String sql = queryUtil.getQueryStudentScoreByTeacherSql();
        System.out.println("Please enter the name of teacher: ");
        String name = sc_exam.next();
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            while(rs.next()){
                System.out.println(String.format("老师：%s, 课程：%s, 学生：%s, 分数：%d",
                        rs.getString("teacher"), rs.getString("class_name"), rs.getString("student"), rs.getInt("score")));
            }
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryAllClasses(){
        String sql = queryUtil.getQueryAllSql()+"class";
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Clazz clazz = new Clazz(rs.getString("name"), rs.getInt("teacher_id"),
                        rs.getInt("student_id"), rs.getInt("score"));
                System.out.println(clazz);
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryClassByName(){
        String sql = queryUtil.getQueryClassByNameSql();
        System.out.println("Please enter the name of class: ");
        String name = sc_exam.next();
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            while(rs.next()){
                Clazz clazz = new Clazz(rs.getString("name"), rs.getInt("teacher_id"),
                        rs.getInt("student_id"), rs.getInt("score"));
                System.out.println(clazz);
            }
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryClassByTeacherName(){
        String sql = queryUtil.getQueryClassByTeacherSql();
        System.out.println("Please enter the name of teacher: ");
        String name = sc_exam.next();
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            while(rs.next()){
                Clazz clazz = new Clazz(rs.getString("name"), rs.getInt("teacher_id"),
                        rs.getInt("student_id"), rs.getInt("score"));
                System.out.println(clazz);
            }
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryAllTeachers(){
        String sql = queryUtil.getQueryAllSql()+"teacher";
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Teacher tempTeacher = new Teacher(rs.getInt("teacher_id"), rs.getString("name"),
                        rs.getInt("age"));
                System.out.println(tempTeacher);
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void queryTeacherByName(){
        String sql = queryUtil.getQueryTeacherByNameSql();
        System.out.println("Please enter the name of teacher: ");
        String name = sc_exam.next();
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            while(rs.next()){
                Teacher tempTeacher = new Teacher(rs.getInt("teacher_id"), rs.getString("name"),
                        rs.getInt("age"));
                System.out.println(tempTeacher);
            }
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void addStudent(){
        String sql = addUtil.getAddStudentSql();
        System.out.println("请输入学生信息(例如：学号student_id：1001, 姓名name：小明, 年龄age：18, 性别sex：男)：");
        String input = sc_exam.next();
        int student_id = Integer.parseInt(input.split(",")[0]);
        String name = input.split(",")[1];
        int age = Integer.parseInt(input.split(",")[2]);
        String sex = input.split(",")[3];
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setInt(1, student_id);
            ptmt.setString(2, name);
            ptmt.setInt(3, age);
            ptmt.setString(4, sex);
            Boolean result = ptmt.execute();
            System.out.println("添加学生["+name+student_id+"]成功！");
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void addClass(){
        String sql = addUtil.getAddClassSql();
        System.out.println("请输入课程信息(例如：课程名class_name：Math, 教师工号teacher_id：2001, 学生学号student_id：1001, 分数score：90)：");
        String input = sc_exam.next();
        String className = input.split(",")[0];
        int teacher_id = Integer.parseInt(input.split(",")[1]);
        int student_id = Integer.parseInt(input.split(",")[2]);
        int score = Integer.parseInt(input.split(",")[3]);
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, className);
            ptmt.setInt(2, teacher_id);
            ptmt.setInt(3, student_id);
            ptmt.setInt(4, score);
            Boolean result = ptmt.execute();
            System.out.println("添加课程["+className+"]成功！");
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void updateScore(){
        String sql = updateUtil.getUpdateScoreSql();
        System.out.println("Please enter class name, student name, and new score: ");
        String input = sc_exam.next();
        String className= input.split(",")[0];
        String studentName = input.split(",")[1];
        int score = Integer.parseInt(input.split(",")[2]);
        try{
            Connection conn = DatabaseUtil.getConnection();
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setInt(1, score);
            ptmt.setString(2, className);
            ptmt.setString(3, studentName);
            ptmt.execute();
            System.out.println("Update succeeded!");
            conn.close();
            ptmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deleteStudent(){
        System.out.println("Please enter student id: ");
        String student_id = sc_exam.next();
        String sql = deleteUtil.getDeleteSql("student", "student_id", student_id);
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            System.out.println("删除学生之后，该学生信息将不能恢复，是否要继续删除？\n1. 是\n2. 否");
            int choice = sc_exam.nextInt();
            if(choice==1){
                stmt.execute(sql);
                System.out.println("删除学生["+student_id+"]成功！");
            } else {
                System.out.println("Quit.");
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deleteClass(){
        System.out.println("Please enter class name: ");
        String className = sc_exam.next();
        String sql = deleteUtil.getDeleteSql("class", "name", className);
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            System.out.println("删除课程之后，该课程信息将不能恢复，是否要继续删除？\n1. 是\n2. 否");
            int choice = sc_exam.nextInt();
            if(choice==1){
                stmt.execute(sql);
                System.out.println("删除课程额["+className+"]成功！");
            } else {
                System.out.println("Quit.");
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deleteTeacher(){
        System.out.println("Please enter teacher id: ");
        String teacher_id = sc_exam.next();
        String sql = deleteUtil.getDeleteSql("teacher", "teacher_id", teacher_id);
        try{
            Connection conn = DatabaseUtil.getConnection();
            Statement stmt = conn.createStatement();
            System.out.println("删除教师之后，该教师信息将不能恢复，是否要继续删除？\n1. 是\n2. 否");
            int choice = sc_exam.nextInt();
            if(choice==1){
                stmt.execute(sql);
                System.out.println("删除教师["+teacher_id+"]成功！");
            } else {
                System.out.println("Quit.");
            }
            conn.close();
            stmt.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

}
